package com.mengya.utils;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.common.utils.mapper.JsonMapper;
import com.gzcwt.entity.Notice;
import com.gzcwt.entity.QueryPublicInfoResult;
import com.mengya.ui.Conf;
/**
 * 操作表notice
 * 创建数据库
 */
public  class NoticeDBO {
	public  SQLiteDatabase db;
	public  void createDB(Context context){
		DBOpenHelper dbHelper=new DBOpenHelper(context,Conf.DB_NAME);
		db=dbHelper.getWritableDatabase();
	}

	//向表notice 插入一条记录
	public  void insertIntoNotice(Notice notice){
		ContentValues value=new ContentValues();
		value.put("id", notice.getId());
		value.put("title", notice.getTitle());
		value.put("content", notice.getContent());
		value.put("public_time", notice.getPublicTime());
		value.put("public_depart", notice.getPublicDepart());
		value.put("is_read", 0);
		db.insert("notice", null, value);
	}
	//根据id设置已读公告
	public  void setNoticeReaded(int id){
		ContentValues value=new ContentValues();
		value.put("is_read", 1);
		String[] s = new String[1];
		s[0]=id+"";
		db.update("notice", value, "id=?", s);
		
	}
	//获取未读公告
	public  List<Notice> getNoticeUnread(){
		List<Notice> noticeList = new ArrayList<Notice>();
		Cursor cursor=db.query("notice", new String[]{"id","title","content","public_time","public_depart"}, "is_read=?", new String[]{"0"},null ,null,null);

		while(cursor.moveToNext()){
			Notice notice = new Notice();
			notice.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex("id"))));
			notice.setTitle(cursor.getString(cursor.getColumnIndex("title")));
			notice.setContent(cursor.getString(cursor.getColumnIndex("content")));
			notice.setPublicDepart(cursor.getString(cursor.getColumnIndex("public_depart")));
			notice.setPublicTime(cursor.getString(cursor.getColumnIndex("public_time")));
			noticeList.add(notice);
		}
		return noticeList;
	}
	
	//根据时间获取公告
	public List<Notice> getNoticeByTime(String startTime,String endTime){
		List<Notice> noticeList = new ArrayList<Notice>();
		
		String sql = "SELECT id,title,content,public_depart,public_time FROM  notice WHERE public_time BETWEEN '" + startTime +"' AND '" + endTime +"'";
		Log.i("sql", sql);
		Cursor cursor=db.rawQuery(sql, null);
		
		while(cursor.moveToNext()){
			Notice notice = new Notice();
			notice.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex("id"))));
			notice.setTitle(cursor.getString(cursor.getColumnIndex("title")));
			notice.setContent(cursor.getString(cursor.getColumnIndex("content")));
			notice.setPublicDepart(cursor.getString(cursor.getColumnIndex("public_depart")));
			notice.setPublicTime(cursor.getString(cursor.getColumnIndex("public_time")));
			noticeList.add(notice);
		}
		return noticeList;
	}
	//获取已读公告
	public  List<Notice> getNoticeHis(){
		List<Notice> noticeList = new ArrayList<Notice>();
		Cursor cursor=db.query("notice", new String[]{"id","title","content","public_time","public_depart"}, "is_read=1", null,null ,null,null);

		while(cursor.moveToNext()){
			Notice notice = new Notice();
			notice.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex("id"))));
			notice.setTitle(cursor.getString(cursor.getColumnIndex("title")));
			notice.setContent(cursor.getString(cursor.getColumnIndex("content")));
			notice.setPublicDepart(cursor.getString(cursor.getColumnIndex("public_depart")));
			notice.setPublicTime(cursor.getString(cursor.getColumnIndex("public_time")));
			noticeList.add(notice);
		}
		return noticeList;
	}
	//更新数据库
	public void updateNotice(String result){
		if(result == null){
			return;
		}
		JsonMapper json = JsonMapper.buildNormalMapper();
		QueryPublicInfoResult d2 = json.fromJson(result,
				QueryPublicInfoResult.class);
		if (null != d2) {
			List<Notice> publicinfos = d2.publicinfos;
			if (null != publicinfos && publicinfos.size() > 0) {
				if (publicinfos.size() > 0) {
					for (int i = 0; i < publicinfos.size(); i++) {
						insertIntoNotice(publicinfos.get(i));
					}
				}
			}
		}
	}
	//关闭数据库
	public  void closeDB(){
		db.close();
	}
}